NTISthis.com

Evidence Guide: ICTWEB425 - Apply structured query language to extract and manipulate data

Student: __________________________________________________

Signature: _________________________________________________

Tips for gathering evidence to demonstrate your skills

The important thing to remember when gathering evidence is that the more evidence the better - that is, the more evidence you gather to demonstrate your skills, the more confident an assessor can be that you have learned the skills not just at one point in time, but are continuing to apply and develop those skills (as opposed to just learning for the test!). Furthermore, one piece of evidence that you collect will not usualy demonstrate all the required criteria for a unit of competency, whereas multiple overlapping pieces of evidence will usually do the trick!

From the Wiki University

 

ICTWEB425 - Apply structured query language to extract and manipulate data

What evidence can you provide to prove your understanding of each of the following citeria?

Identify SQL requirements from the specification documentation

  1. Determine the information to be used from the database
  2. Identify the tables that hold this information
  3. Identify the primary keys in these tables
  4. Identify the relationships between these tables, including foreign keys
Determine the information to be used from the database

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Identify the tables that hold this information

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Identify the primary keys in these tables

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Identify the relationships between these tables, including foreign keys

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Create a relational database

  1. Create tables in a database, using SQL statements
  2. Identify the primary and foreign keys for database table
  3. Manipulate the data in a database, using SQL statements
  4. Query the database, using SQL statements
  5. Write SQL statements to retrieve information from the database
Create tables in a database, using SQL statements

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Identify the primary and foreign keys for database table

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Manipulate the data in a database, using SQL statements

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Query the database, using SQL statements

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Write SQL statements to retrieve information from the database

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Test SQL results

  1. Construct test data to test SQL statements
  2. Determine the expected results in order to verify SQL statements
  3. Verify the result of the constructed SQL statements, against the expected results
Construct test data to test SQL statements

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Determine the expected results in order to verify SQL statements

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Verify the result of the constructed SQL statements, against the expected results

Completed
Date:

Teacher:		 Evidence:

 

 

 

 

 

 

 

Assessed

Teacher: ___________________________________ Date: _________

Signature: ________________________________________________

Comments:

 

 

 

 

 

 

 

 

Instructions to Assessors

Evidence Guide

ELEMENT

PERFORMANCE CRITERIA

Elements describe the essential outcomes.

Performance criteria describe the performance needed to demonstrate achievement of the element.

1. Identify SQL requirements from the specification documentation

1.1 Determine the information to be used from the database

1.2 Identify the tables that hold this information

1.3 Identify the primary keys in these tables

1.4 Identify the relationships between these tables, including foreign keys

2. Create a relational database

2.1 Create tables in a database, using SQL statements

2.2 Identify the primary and foreign keys for database table

2.3 Manipulate the data in a database, using SQL statements

2.4 Query the database, using SQL statements

2.5 Write SQL statements to retrieve information from the database

3. Test SQL results

3.1 Construct test data to test SQL statements

3.2 Determine the expected results in order to verify SQL statements

3.3 Verify the result of the constructed SQL statements, against the expected results

Required Skills and Knowledge

ELEMENT

PERFORMANCE CRITERIA

Elements describe the essential outcomes.

Performance criteria describe the performance needed to demonstrate achievement of the element.

1. Identify SQL requirements from the specification documentation

1.1 Determine the information to be used from the database

1.2 Identify the tables that hold this information

1.3 Identify the primary keys in these tables

1.4 Identify the relationships between these tables, including foreign keys

2. Create a relational database

2.1 Create tables in a database, using SQL statements

2.2 Identify the primary and foreign keys for database table

2.3 Manipulate the data in a database, using SQL statements

2.4 Query the database, using SQL statements

2.5 Write SQL statements to retrieve information from the database

3. Test SQL results

3.1 Construct test data to test SQL statements

3.2 Determine the expected results in order to verify SQL statements

3.3 Verify the result of the constructed SQL statements, against the expected results

Evidence of the ability to:

identify structured query language (SQL) requirements

create the databases and tables

generate queries for one or more tables, to provide the required data

add, modify and delete records from the database tables

test and verify SQL statements.

Note: If a specific volume or frequency is not stated, then evidence must be provided at least once.

To complete the unit requirements safely and effectively, the individual must:

explain the features and application of the following aggregate functions:

MIN

MAX

SUM

AVG

COUNT

COUNT(*)

explain the features and application of the following clause functions:

GROUP BY

HAVING

ORDER BY

dates and times

SQL data types

numbers

text

SQL syntax:

SELECT

FROM

WHERE

LIKE

DISTINCT

CREATE

ALTER TABLE

INSERT INTO

UPDATE

DELETE

DROP

describe the principles of "combining and/or condition" in SQL statements and Boolean operators:

IN and BETWEEN conditional operators

mathematical operators

table joins (relationships).